﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using INSTA.Library;

namespace INSTA.BankApplication.DAL
{
    /// <summary>
    /// Base class for SQL statements generation.
    /// </summary>
    public abstract class AbstractSqlGenerator
    {
        private DataMapper _mapper;

        private const string CRLF = "\r\n";


        /// <summary>
        /// Initializes a new instance of 
        /// the <see cref="AbstractSqlGenerator"/> class.
        /// </summary>
        /// <param name="mapper">The mapper.</param>
        public AbstractSqlGenerator(DataMapper mapper)
        {
            _mapper = mapper;
        }


        /// <summary>
        /// Generates the SQL for the SELECT clause.
        /// </summary>
        /// <returns> the SELECT clause</returns>
        public virtual string GenerateSQLFor_Select()
        {
            var sb = new StringBuilder();

            sb.AppendFormat("SELECT * FROM [{0}] ", GetTableName());

            sb.Append(GetWhereCondition());

            return sb.ToString();
        }
        
        /// <summary>
        /// Generates the SQL for the DELETE clause.
        /// </summary>
        /// <returns> the DELETE clause</returns>
        public virtual string GenerateSQLFor_Delete()
        {
            var sb = new StringBuilder();

            sb.AppendFormat("DELETE FROM [{0}] ", GetTableName());

            sb.Append(GetWhereCondition());
            
            return sb.ToString();
        }
        
        /// <summary>
        /// Generates the SQL for the INSERT clause.
        /// </summary>
        /// <returns> the INSERT clause</returns>

        public virtual string GenerateSQLFor_Insert()
        {
            var sb = new StringBuilder();
            
            var tableName = GetTableName();
            sb.AppendFormat("INSERT INTO [{0}] ", tableName);

            sb.Append("(");

            // Loop on all MappedProperties (except the primary key) for 
            // field names.
            foreach (var mappedProp in _mapper.MappedProperties.Values)
            {
                if (mappedProp.IsPrimaryKey == false)
                {
                    sb.AppendFormat("{0}, ", mappedProp.FieldName); 
                }
            }
            // Get rid of the extra ", "..
            sb.Remove(sb.Length - 2, 2);
            sb.Append(") ");

            sb.Append("VALUES (");
            // Loop on all MappedProperties (except the primary key) for 
            // field Values.
            foreach (var mappedProp in _mapper.MappedProperties.Values)
            {
                if (mappedProp.IsPrimaryKey == false)
                {
                    sb.AppendFormat("{0}, ", FormatParameter(mappedProp.PropertyValue));
                }
            }
             // Get rid of the extra ", "..
            sb.Remove(sb.Length - 2, 2);
            sb.Append(");");
            
            return sb.ToString();

        }

        /// <summary>
        /// Generates the SQL for the UPDATE clause.
        /// </summary>
        /// <returns> the UPDATE clause.</returns>
        public virtual string GenerateSQLFor_Update()
        {
            var sb = new StringBuilder();


            var tableName = GetTableName();
            sb.AppendFormat("UPDATE [{0}] SET ", tableName);

            

            // Loop on all MappedProperties (except the primary key) for 
            // field names.
            foreach (var mappedProp in _mapper.MappedProperties.Values)
            {
                if (mappedProp.IsPrimaryKey == false)
                {
                    sb.AppendFormat("{0} = {1}, ", 
                                    mappedProp.FieldName, 
                                    FormatParameter(mappedProp.PropertyValue));
                }
            }
            // Get rid of the extra ", "..
            sb.Remove(sb.Length - 2, 2);
            sb.Append(" ");
            
            sb.Append(GetWhereCondition());


            return sb.ToString();
        }




        /// <summary>
        /// Generates the SQL to get the last generated id, generated 
        /// by the database engine.
        /// </summary>
        /// <returns>The last generated id</returns>
        public abstract string GenerateSQLFor_LastGeneratedId();



        
        /// <summary>
        /// Gets the where condition.
        /// </summary>
        /// <returns>The SQL where condition.</returns>
        private string GetWhereCondition()
        {
            var sb = new StringBuilder();

            var primaryKeyMappedProperty = _mapper.MappedProperties[GetPrimaryKeyPropertyName()];

            var primaryKeyFieldName = primaryKeyMappedProperty.FieldName;
            var primaryKeyValue = primaryKeyMappedProperty.PropertyValue;

            sb.AppendFormat("WHERE {0} = {1};", primaryKeyFieldName, primaryKeyValue);


            return sb.ToString();   
        }

        /// <summary>
        /// Formats the parameter for SQL.
        /// </summary>
        /// <param name="paramValue">The parameter value.</param>
        /// <returns>the parameter value, formatted for SQL.</returns>
        public virtual string FormatParameter(object paramValue)
        {
            const string formatString = "{0}{1}{0}";
            var delimiter        = "";

            if (paramValue is string)
            {
                delimiter = "'";
            }
            else if (paramValue is DateTime)
            {
                delimiter = "#";
            }

            return string.Format(formatString, delimiter, paramValue);

        }


        /// <summary>
        /// Gets the name of the table of the mapped object
        /// </summary>
        /// <returns>
        ///     the name of the table of the mapped object
        /// </returns>
        public string GetTableName()
        {
            string tableName = "";

            // if the current instance is decorated with 
            // a TableAttribute..
            if (_mapper.IsDecoratedWith<TableAttribute>())
            {
                // get the TableAttribute..
                var attribute = _mapper.GetUniqueAttribute<TableAttribute>();

                // Get the TableName property.
                tableName = attribute.TableName;
            }
            else
            {
                // throw an Exception indicating that the 
                // current instance is not decorated with a
                // TableAttribute.
                throw new MissingTableAttributeException("Classe héritant de DataMapper, mais non décorée avec TableAttribute");
            }

            return tableName;
        }

        /// <summary>
        /// Gets the name of the primary key property.
        /// </summary>
        /// <returns>the name of the primary key property.</returns>
        /// <exception cref="System.Exception">Deux propriétés ont la valeur IsPrimaryKey à true</exception>
        public string GetPrimaryKeyPropertyName()
        {
            string keyName = "";

            var query = (from prop in _mapper.MappedProperties.Values
                         where prop.IsPrimaryKey == true
                         select prop.PropertyName).ToList();

            if (query.Count() > 1)
            {
                throw new DuplicatePrimaryKeyException("Deux propriétés ont la valeur IsPrimaryKey à true");
            }
            else
            {
                keyName = query.First();
            }

            return keyName;
        }




    }
}
